package com.data.migration.service;

import com.data.migration.entity.Operation;
import com.data.migration.util.DBUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by Peter on 2018/12/8.
 * 迁移1v1课程数据
 */
public class Step_1_Course {
    public static final String step = "1";
    public static final String stepDesc = "1v1课程";

    private static final Logger logger = LoggerFactory.getLogger(Step_1_Course.class);
    public static List<Operation> insertCourseSql = new ArrayList<>();//插入课程的sql

    public static void main(String[] args) throws SQLException {
        Step_1_Course step_1Course = new Step_1_Course();
//        step_1Course.start();
//        step_1Course.clean();
    }

    public void start() throws SQLException {
        logger.info("\n******************************************************************************" +
                "\n======================== Step Step 1: 迁移课程 start =========================" +
                "\n******************************************************************************");

        // 1. 课程分类【ts_nt_category】
        step_1();
        // 2. 课程 【ts_nt_course_info】
        step_2();
        step_2_2();

        // 3. 课程图片（教材）【td_nt_course_picture】
        step_3();
        step_3_2();

        // 4. 课程Level（单元）【td_nt_course_level】
        step_4();
        step_4_2();

        // 5. 课程目录 【td_nt_course_catalog】
        step_5();
        step_5_2();

        // 6. 课程子目录 【td_nt_course_catalog_son】
        step_6();
        step_6_2();

        // 7. 套餐 【ts_nt_setmeal】
        step_7();
        step_7_2();

        // 8. Banner 【td_nt_banner_info】
        step_8();
        logger.info("\n******************************************************************************" +
                "\n======================== Step 1: 迁移课程 end ================================" +
                "\n******************************************************************************\n");
    }

    public void clean() throws SQLException {
        logger.info("\n******************************************************************************" +
                "\n======================== 【clean】 Step 1: 迁移课程 start =========================" +
                "\n******************************************************************************");
        // 1. 课程分类【ts_nt_category】
        step_1_clean();

        /// 2. 课程 【ts_nt_course_info】
        step_2_clean();

        // 3. 课程图片（教材）【td_nt_course_picture】
        step_3_clean();

        // 4. 课程Level（单元）【td_nt_course_level】
        step_4_clean();

        // 5. 课程目录 【td_nt_course_catalog】
        step_5_clean();

        // 6. 课程子目录 【td_nt_course_catalog_son】
        step_6_clean();

        // 7. 套餐 【ts_nt_setmeal】
        step_7_clean();

        // 8. Banner 【td_nt_banner_info】
        step_8_clean();
        logger.info("\n******************************************************************************" +
                "\n======================== 【clean】 Step 1: 迁移课程 end ================================" +
                "\n******************************************************************************\n");
    }

    // 1. 课程分类【ts_nt_category】     ------------------------------------------------------------------------------1
    public void step_1() throws SQLException {
        logger.info("\n     --------- 1.1 课程分类【ts_nt_category】 start --------- ");
        Connection conn = DBUtil.getConn(Operation.app_type_T);
        String tableName = "ts_nt_category"; // 表名
        String pk = "cate_ID"; // 主键（自增）的列名字
        insertCourseSql = DBUtil.buildInsertSql(conn, tableName, pk, step, stepDesc);

        DBUtil.insertBatch(insertCourseSql);
    }

    public void step_1_clean() throws SQLException {
        logger.info("\n     ---------【clean】 1.3 课程分类【ts_nt_category】 start --------- ");
        String sql = "delete from ts_nt_category where app_type='T'";
        Connection conn = DBUtil.getConn(Operation.app_type_A);
        DBUtil.executeUpdate(conn, sql);
    }

    // 2. 课程 【ts_nt_course_info】     ----------------------------------------------------------------------------2
    public void step_2() throws SQLException {
        logger.info("\n     --------- 1.2 课程 【ts_nt_course_info】 start --------- ");
        Connection conn = DBUtil.getConn(Operation.app_type_T);
        String tableName = "ts_nt_course_info"; // 表名
        String pk = "cou_ID"; // 主键（自增）的列名字
        insertCourseSql = DBUtil.buildInsertSql(conn, tableName, pk, step, stepDesc);

        DBUtil.insertBatch(insertCourseSql);
    }

    // 2.2 更新外键【ts_nt_course_info.cate_id】
    public void step_2_2() throws SQLException {
        String sql = "UPDATE ts_nt_course_info as A\n" +
                "INNER JOIN (select cate_id,t_id,app_type from ts_nt_category where app_type='T') as B\n" +
                "ON A.cate_id = B.t_id\n" +
                "SET A.cate_id=B.cate_id\n" +
                "where A.app_type='T'";
        Connection conn = DBUtil.getConn(Operation.app_type_A);
        DBUtil.executeUpdate(conn, sql);
    }

    public void step_2_clean() throws SQLException {
        logger.info("\n     ---------【clean】 1.3 课程 【ts_nt_course_info】 start --------- ");
        String sql = "delete from ts_nt_course_info where app_type='T'";
        Connection conn = DBUtil.getConn(Operation.app_type_A);
        DBUtil.executeUpdate(conn, sql);
    }

    // 3. 课程图片（教材）【td_nt_course_picture】     --------------------------------------------------------------------3
    public void step_3() throws SQLException {
        logger.info("\n     --------- 1.3 课程图片（教材）【td_nt_course_picture】 start --------- ");
        Connection conn = DBUtil.getConn(Operation.app_type_T);
        String tableName = "td_nt_course_picture"; // 表名
        String pk = "pic_ID"; // 主键（自增）的列名字
        insertCourseSql = DBUtil.buildInsertSql(conn, tableName, pk, step, stepDesc);

        DBUtil.insertBatch(insertCourseSql);
    }

    // 3.2. 更新外键【td_nt_course_picture.cou_id】
    public void step_3_2() throws SQLException {
        String sql = "UPDATE td_nt_course_picture as A\n" +
                "INNER JOIN (select cou_id,t_id,app_type from ts_nt_course_info where app_type='T') as B\n" +
                "ON A.cou_id = B.t_id\n" +
                "SET A.cou_id=B.cou_id\n" +
                "where A.app_type='T'";
        Connection conn = DBUtil.getConn(Operation.app_type_A);
        DBUtil.executeUpdate(conn, sql);
    }

    public void step_3_clean() throws SQLException {
        logger.info("\n     ---------【clean】 1.3 课程图片（教材）【td_nt_course_picture】 start --------- ");
        String sql = "delete from td_nt_course_picture where app_type='T'";
        Connection conn = DBUtil.getConn(Operation.app_type_A);
        DBUtil.executeUpdate(conn, sql);
    }

    // 4. 课程Level（单元）【td_nt_course_level】     --------------------------------------------------------------4
    public void step_4() throws SQLException {
        logger.info("\n     --------- 1.4 课程Level（单元）【td_nt_course_level】 start --------- ");
        Connection conn = DBUtil.getConn(Operation.app_type_T);
        String tableName = "td_nt_course_level"; // 表名
        String pk = "level_id"; // 主键（自增）的列名字
        insertCourseSql = DBUtil.buildInsertSql(conn, tableName, pk, step, stepDesc);

        DBUtil.insertBatch(insertCourseSql);
    }

    // 4.2. 更新外键【td_nt_course_level.cou_id】
    public void step_4_2() throws SQLException {
        String sql = "UPDATE td_nt_course_level as A\n" +
                "INNER JOIN (select cou_id,t_id,app_type from ts_nt_course_info where app_type='T') as B\n" +
                "ON A.cou_id = B.t_id\n" +
                "SET A.cou_id=B.cou_id\n" +
                "where A.app_type='T'";
        Connection conn = DBUtil.getConn(Operation.app_type_A);
        DBUtil.executeUpdate(conn, sql);
    }

    public void step_4_clean() throws SQLException {
        logger.info("\n     ---------【clean】 1.4 课程Level（单元）【td_nt_course_level】 start --------- ");
        String sql = "delete from td_nt_course_level where app_type='T'";
        Connection conn = DBUtil.getConn(Operation.app_type_A);
        DBUtil.executeUpdate(conn, sql);
    }


    // 5. 课程目录 【td_nt_course_catalog】     --------------------------------------------------------------------5
    public void step_5() throws SQLException {
        logger.info("\n     --------- 1.5 课程目录 【td_nt_course_catalog】 start --------- ");
        Connection conn = DBUtil.getConn(Operation.app_type_T);
        String tableName = "td_nt_course_catalog"; // 表名
        String pk = "cata_id"; // 主键（自增）的列名字
        insertCourseSql = DBUtil.buildInsertSql(conn, tableName, pk, step, stepDesc);

        DBUtil.insertBatch(insertCourseSql);
    }

    // 5.2. 更新外键【td_nt_course_catalog.cou_id】 、【td_nt_course_catalog.level_id】
    public void step_5_2() throws SQLException {
        // 更新 cou_id
        String sql = "UPDATE td_nt_course_catalog as A\n" +
                "INNER JOIN (select cou_id,t_id,app_type from ts_nt_course_info where app_type='T') as B\n" +
                "ON A.cou_id = B.t_id\n" +
                "SET A.cou_id=B.cou_id\n" +
                "where A.app_type='T'";

        // 更新 level_id
        String sql2 = "UPDATE td_nt_course_catalog as A\n" +
                "INNER JOIN (select level_id,t_id,app_type from td_nt_course_level where app_type='T') as B\n" +
                "ON A.level_id = B.t_id\n" +
                "SET A.level_id=B.level_id\n" +
                "where A.app_type='T'";
        Connection conn = DBUtil.getConn(Operation.app_type_A);

        DBUtil.executeUpdate(conn, sql);
        DBUtil.executeUpdate(conn, sql2);
    }

    public void step_5_clean() throws SQLException {
        logger.info("\n     ---------【clean】 1.5 课程目录 【td_nt_course_catalog】 start --------- ");
        String sql = "delete from td_nt_course_catalog where app_type='T'";
        Connection conn = DBUtil.getConn(Operation.app_type_A);
        DBUtil.executeUpdate(conn, sql);
    }

    // 6. 课程子目录 【td_nt_course_catalog_son】     --------------------------------------------------------------6
    public void step_6() throws SQLException {
        logger.info("\n     --------- 1.6 课程子目录 【td_nt_course_catalog_son】 start --------- ");
        Connection conn = DBUtil.getConn(Operation.app_type_T);
        String tableName = "td_nt_course_catalog_son"; // 表名
        String pk = "cata_son_id"; // 主键（自增）的列名字
        insertCourseSql = DBUtil.buildInsertSql(conn, tableName, pk, step, stepDesc);

        DBUtil.insertBatch(insertCourseSql);
    }

    // 6.2. 更新外键【td_nt_course_catalog_son.cata_id】
    public void step_6_2() throws SQLException {
        String sql = "UPDATE td_nt_course_catalog_son as A\n" +
                "INNER JOIN (select cata_id,t_id,app_type from td_nt_course_catalog where app_type='T') as B\n" +
                "ON A.cata_id = B.t_id\n" +
                "SET A.cata_id=B.cata_id\n" +
                "where A.app_type='T';";
        Connection conn = DBUtil.getConn(Operation.app_type_A);

        DBUtil.executeUpdate(conn, sql);
    }

    public void step_6_clean() throws SQLException {
        logger.info("\n     ---------【clean】 1.6 课程子目录 【td_nt_course_catalog_son】 start --------- ");
        String sql = "delete from td_nt_course_catalog_son where app_type='T'";
        Connection conn = DBUtil.getConn(Operation.app_type_A);
        DBUtil.executeUpdate(conn, sql);
    }

    // 7. 套餐 【ts_nt_setmeal】     -------------------------------------------------------------------------------7
    public void step_7() throws SQLException {
        logger.info("\n     --------- 1.7 套餐 【ts_nt_setmeal】 start --------- ");
        Connection conn = DBUtil.getConn(Operation.app_type_T);
        String tableName = "ts_nt_setmeal"; // 表名
        String pk = "set_id"; // 主键（自增）的列名字
        insertCourseSql = DBUtil.buildInsertSql(conn, tableName, pk, step, stepDesc);

        DBUtil.insertBatch(insertCourseSql);
    }

    // 7.2. 更新外键【td_nt_course_catalog_son.cata_id】
    public void step_7_2() throws SQLException {
        String sql = "UPDATE ts_nt_setmeal as A\n" +
                "INNER JOIN (select cou_id,t_id,app_type from ts_nt_course_info where app_type='T') as B\n" +
                "ON A.cou_id = B.t_id\n" +
                "SET A.cou_id=B.cou_id\n" +
                "where A.app_type='T';";
        Connection conn = DBUtil.getConn(Operation.app_type_A);

        DBUtil.executeUpdate(conn, sql);
    }

    public void step_7_clean() throws SQLException {
        logger.info("\n     ---------【clean】 1.7 套餐 【ts_nt_setmeal】 start --------- ");
        String sql = "delete from ts_nt_setmeal where app_type='T'";
        Connection conn = DBUtil.getConn(Operation.app_type_A);
        DBUtil.executeUpdate(conn, sql);
    }

    // 8. Banner 【td_nt_banner_info】     -------------------------------------------------------------------------------8
    public void step_8() throws SQLException {
        logger.info("\n     --------- 1.8  Banner 【td_nt_banner_info】 start --------- ");
        Connection conn = DBUtil.getConn(Operation.app_type_T);
        String tableName = "td_nt_banner_info"; // 表名
        String pk = "BANNER_ID"; // 主键（自增）的列名字
        insertCourseSql = DBUtil.buildInsertSql(conn, tableName, pk, step, stepDesc);

        DBUtil.insertBatch(insertCourseSql);
    }

    public void step_8_clean() throws SQLException {
        logger.info("\n     ---------【clean】 1.8 Banner 【td_nt_banner_info】 start --------- ");
        String sql = "delete from td_nt_banner_info where app_type='T'";
        Connection conn = DBUtil.getConn(Operation.app_type_A);
        DBUtil.executeUpdate(conn, sql);
    }

}
